<?php
class CongtackenhsController extends AppController {

	var $name = 'Congtackenhs';
	var $helpers=array('Html','Form','Js','Others');
	var $uses = array('Congtackenh','Thuocdv','Donvi','Phankenh','Chamsockenh','Thitruongkenh','Nhanvien','Zdv');
    var $components = array('RequestHandler','Cookie');

	function index($kenh_id) {
		$this->Congtackenh->recursive = 0;
		$this->set('congtackenhs', $this->paginate(array('kenh_id'=>$kenh_id)));
        $kenhs=$this->Congtackenh->query('select * from kenhs where id='.$kenh_id);
		$this->set(compact('kenh_id','kenhs'));
	}

	function view($id = null) {
		if (!$id) {
			$this->Session->setFlash(__('Invalid congtackenh', true));
			$this->redirect(array('action' => 'index'));
		}
		$this->set('congtackenh', $this->Congtackenh->read(null, $id));
	}

	function add($kenh_id) {
		if (!empty($this->data)) {
			$this->Congtackenh->create();
            //$record['Congtackenh']['ngaytao']=date('Y-m-d H:i:s');
			if ($this->Congtackenh->save($this->data)) {
				$this->Session->setFlash(__('Thông tin đã cập nhật', true));
				$this->redirect(array('action' => 'index',$kenh_id));
			} else {
				$this->Session->setFlash(__('Thông tin không thể cập nhật', true));
			}
		}
		$kenhs = $this->Congtackenh->Kenh->find('list');
		$this->set(compact('kenhs','kenh_id'));
	}

	function edit($id = null,$kenh_id=null) {
		if (!$id && empty($this->data)) {
			$this->Session->setFlash(__('Invalid congtackenh', true));
			$this->redirect(array('action' => 'index',$kenh_id));
		}
		if (!empty($this->data)) {
		  
			if ($this->Congtackenh->save($this->data)) {
				$this->Session->setFlash(__('Thông tin đã cập nhật', true));
				$this->redirect(array('action' => 'index'));
			} else {
				$this->Session->setFlash(__('Thông tin không thể cập nhật', true));
			}
		}
		if (empty($this->data)) {
			$this->data = $this->Congtackenh->read(null, $id);
		}
		$kenhs = $this->Congtackenh->Kenh->find('list');
		$this->set(compact('kenhs'));
	}

	function delete($id = null,$kenh_id=null) {
		if (!$id) {
			$this->Session->setFlash(__('Invalid id for congtackenh', true));
			$this->redirect(array('action'=>'index'));
		}
		if ($this->Congtackenh->delete($id)) {
			$this->Session->setFlash(__('Thông tin đã xóa', true));
			$this->redirect(array('action'=>'index',$kenh_id));
		}
		$this->Session->setFlash(__('Thông tin không thể xóa', true));
		$this->redirect(array('action' => 'index',$kenh_id));
	}
    
    function update_hinhanh(){
        if ($this->data) {
            App::import('Core', 'sanitize');
            $title = Sanitize::clean($this->data['Congtackenh']['hinhanh']);
            $this->Congtackenh->id = $this->data['Congtackenh']['id'];
            $this->Congtackenh->saveField('hinhanh', $title);
            $this->set('posttitle', $title);
        }
    }
    
    function update_poster(){
        if ($this->data) {
            App::import('Core', 'sanitize');
            $title = Sanitize::clean($this->data['Congtackenh']['poster']);
            $this->Congtackenh->id = $this->data['Congtackenh']['id'];
            $this->Congtackenh->saveField('poster', $title);
            $this->set('posttitle', $title);
        }
    }
    
    function update_toroi(){
        if ($this->data) {
            App::import('Core', 'sanitize');
            $title = Sanitize::clean($this->data['Congtackenh']['toroi']);
            $this->Congtackenh->id = $this->data['Congtackenh']['id'];
            $this->Congtackenh->saveField('toroi', $title);
            $this->set('posttitle', $title);
        }
    }

    /*
     * thong tin kenh
     */
    
    function theodoidb($donvi_id=null,$nhanvien_id=null)
    {
    	
    	$str = substr($this->Session->read('cur_tendv'),0,2);
    	if($str!='TT')
    	{
    		$donvi_cha = $this->Session->read('cur_cha');
    		$donvis = $this->Thuocdv->find('list',array(
    										'recursive'=>0,
    										'conditions'=>array('Thuocdv.donvi_id'=>$donvi_cha,'left(Donvi.madv,2)'=>'TT'),
    										'fields'=>array('Donvi.id','Donvi.tendv')));
    		$donvi_id = array_keys($donvis);
    	}
    	else
    	{
    		$donvis = $this->Donvi->find('list',array('conditions'=>array('id'=>$donvi_id),
    													'fields'=>array('id','tendv')));
    	}
    	if(!$nhanvien_id)
    	{
    		$nhanviens = $this->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),
    															'fields'=>array('id','tennv')));
    		$nhanvien_id = array_keys($nhanviens);
    	}
    	
    	$kenhs = $this->Phankenh->find('list',array(
    												'recursive'=>0,
    												'conditions'=>array('nhanvien_id'=>$nhanvien_id,'ngaykt_hd'=>null),
    												'fields'=>array('Kenh.id','Kenh.tenkenh')));
    	$nams = $this->Chamsockenh->find('list',array('fields'=>array('year(Chamsockenh.ngaydi)'),
    													'group'=>array('year(Chamsockenh.ngaydi)')));
    	$nams = array(date('Y')=>date('Y'));
    	
    	$this->set(compact('donvis','kenhs','nhanviens','nams'));
    }
    
    function laykq()
    {
    	
        $this->Cookie->write('cdonvi_id',$this->data['Congtackenh']['donvi_id'],100);
    	$this->Cookie->write('cnam',$this->data['Congtackenh']['nam'],100);
    	$this->Cookie->write('cthang',$this->data['Congtackenh']['thang'],100);
    	
    	
    	
        
        $this->layout = 'ajax';
    	$donvi_id = $this->data['Congtackenh']['donvi_id'];
        
        
    	$nam =$this->data['Congtackenh']['nam'];
        
    	$thang =$this->data['Congtackenh']['thang'];
    	$cuoithang = $this->endofmonth($thang,$nam);
    	$dauthang = date('Y-m-d',strtotime("{$nam}-{$thang}-01"));
    	$donvi_id = $this->data['Congtackenh']['donvi_id'];
    	
    	if(isset($this->data['Congtackenh']['kenh'])){
    	    
    		$kenh_id = $this->data['Congtackenh']['kenh'];
            $this->Cookie->write('ckenhc',$this->data['Congtackenh']['kenh'],100);
        }
    	else{
    	   
    		$kenh_id = $this->data['kenh'];
            $this->Cookie->write('ckenh',$this->data['kenh'],100);
   		}
    		
    	if(empty($kenh_id))
    	{
	    	if(isset($this->data['Congtackenh']['nhanvien_id']))
	    	{
	    		if(empty($this->data['Congtackenh']['nhanvien_id']))
	    		{
	    			if(empty($donvi_id))
					{
						$donvi_cha = $this->Session->read('cur_cha');
						$donvi_id = $this->Thuocdv->find('list',array('conditions'=>array('donvi_id'=>$donvi_cha)));
					}
				$nhanvien_id = $this->Donvi->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),
																		'fields'=>array('id','id')));
	    		}
	    		else
	    		{
	    			$nhanvien_id = $this->data['Congtackenh']['nhanvien_id'];
                    $this->Cookie->write('cnhanvien_idc',$this->data['Congtackenh']['nhanvien_id'],100);
	   	
	    		}
	    	}
	    	else
	    	{
	    		if(empty($this->data['nhanvien_id']))
	    		{
	    			if(empty($donvi_id))
					{
						$donvi_cha = $this->Session->read('cur_cha');
						$donvi_id = $this->Thuocdv->find('list',array('conditions'=>array('donvi_id'=>$donvi_cha)));
					}
				$nhanvien_id = $this->Donvi->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),
																		'fields'=>array('id','id')));
	    		}
	    		else
	    		{
	    			$nhanvien_id = $this->data['nhanvien_id'];
                    
       	            $this->Cookie->write('cnhanvien',$this->data['nhanvien_id'],100);
        
	    		}
	    	}
	    	$kenh_id= $this->Phankenh->find('list',array(
														'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																			'ngaykt_hd'=>null),
														'fields'=>array('kenh_id','kenh_id')));
    	}
    	
		//$this->Donvi->unbindModel(array('hasMany'=>array('Chitieudv','Kehoach','Thuocdv','Nhanvien')));
		$this->Phankenh->unbindModel(array('hasMany'=>array('Kqbanhang')));
		$this->Nhanvien->unbindModel(array('belongsTo'=>array('Phankenh','Chucdanh'),'hasMany'=>array('Chitieunv','Phankenh','Nvchithi','Nvngay','Phankenh','Tempnhiemvunv','User')));
    	$this->Congtackenh->Kenh->unbindModel(array('belongsTo'=>array('Loaikenh','Donvi'),'hasMany'=>array('Phankenh')));//
    	//$this->Congtackenh->Kenh->bindModel(array('hasMany'=>array('Phankenh')));
    	$kenhinfo = $this->Phankenh->find('all',array('recursive'=>2,'conditions'=>array('Kenh.id'=>$kenh_id),'order'=>array('Phankenh.nhanvien_id')));
    	
    	
    	$qstrcongtacdbs = "select *
    			from congtackenhs Congtackenh 
    			where Congtackenh.ngaytao >= '".$dauthang."' and Congtackenh.ngaytao <= '".$cuoithang."'   
    			AND	kenh_id in (".implode(",",$kenh_id).") and Congtackenh.ngaytao in 
    						(select max(ngaytao) 
    						from congtackenhs ctksub 
    						where ctksub.kenh_id=Congtackenh.kenh_id
    							and ctksub.ngaytao >= '".$dauthang."' 
    							and ctksub.ngaytao <= '".$cuoithang."')";
 
    	$congtackenhs = $this->Congtackenh->query($qstrcongtacdbs);
    	
    	
    	$thongtintts = $this->Thitruongkenh->find('all',array('conditions'=>array('Thitruongkenh.ngaytao >='=>$dauthang,
    																' Thitruongkenh.ngaytao <='=>$cuoithang),
    											'fields'=>array('Kenh_id',"GROUP_CONCAT(Thitruongkenh.thitruong SEPARATOR '<br> - ') as thitruong" )));
    	$chamsocs = $this->Chamsockenh->find('list',array('conditions'=>array('kenh_id'=>$kenh_id,'ngaydi >='=>$dauthang , 'ngaydi <='=>$cuoithang ),
    													'fields'=>array('kenh_id','ngaydi'),
    													'group'=>array('kenh_id','ngaydi')));
    	/*
    	$qstrthitruong ="select kenh_id, group_concat(thitruong) 
    					from thitruongkenhs as thitruongkenh
    					where kenh_id in (". implode(',', $kenh_id) .") and ngaytao >= '".$dauthang."' and ngaytao <= '".$cuoithang."'";
    	
    	$thongtintts = $this->Congtackenh->query($qstrthitruong);
    	*/
    	$this->set(compact('kenhinfo','congtackenhs','thongtintts','chamsocs','dauthang','cuoithang'));
        $this->set(compact('donvi_id','nam','thang'));
    
    	
    }
    
    function laykqe()
    {
    	
        
        $this->layout = '';
    	$donvi_id = $this->data['Congtackenh']['donvi_id'];
        
        
    	$nam =$this->data['Congtackenh']['nam'];
        
    	$thang =$this->data['Congtackenh']['thang'];
    	$cuoithang = $this->endofmonth($thang,$nam);
    	$dauthang = date('Y-m-d',strtotime("{$nam}-{$thang}-01"));
    	$donvi_id = $this->data['Congtackenh']['donvi_id'];
    	
    	if(isset($this->data['Congtackenh']['kenh'])){
    		$kenh_id = $this->data['Congtackenh']['kenh'];
        }
    	else
    		$kenh_id = $this->data['kenh'];
    		
    		
    	if(empty($kenh_id))
    	{
	    	if(isset($this->data['Congtackenh']['nhanvien_id']))
	    	{
	    		if(empty($this->data['Congtackenh']['nhanvien_id']))
	    		{
	    			if(empty($donvi_id))
					{
						$donvi_cha = $this->Session->read('cur_cha');
						$donvi_id = $this->Thuocdv->find('list',array('conditions'=>array('donvi_id'=>$donvi_cha)));
					}
				$nhanvien_id = $this->Donvi->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),
																		'fields'=>array('id','id')));
	    		}
	    		else
	    		{
	    			$nhanvien_id = $this->data['Congtackenh']['nhanvien_id'];
	    		}
	    	}
	    	else
	    	{
	    		if(empty($this->data['nhanvien_id']))
	    		{
	    			if(empty($donvi_id))
					{
						$donvi_cha = $this->Session->read('cur_cha');
						$donvi_id = $this->Thuocdv->find('list',array('conditions'=>array('donvi_id'=>$donvi_cha)));
					}
				$nhanvien_id = $this->Donvi->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),
																		'fields'=>array('id','id')));
	    		}
	    		else
	    		{
	    			$nhanvien_id = $this->data['nhanvien_id'];
	    		}
	    	}
	    	$kenh_id= $this->Phankenh->find('list',array(
														'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																			'ngaykt_hd'=>null),
														'fields'=>array('kenh_id','kenh_id')));
    	}
    	
		//$this->Donvi->unbindModel(array('hasMany'=>array('Chitieudv','Kehoach','Thuocdv','Nhanvien')));
		$this->Phankenh->unbindModel(array('hasMany'=>array('Kqbanhang')));
		$this->Nhanvien->unbindModel(array('belongsTo'=>array('Phankenh','Chucdanh'),'hasMany'=>array('Chitieunv','Phankenh','Nvchithi','Nvngay','Phankenh','Tempnhiemvunv','User')));
    	$this->Congtackenh->Kenh->unbindModel(array('belongsTo'=>array('Loaikenh','Donvi'),'hasMany'=>array('Phankenh')));//
    	//$this->Congtackenh->Kenh->bindModel(array('hasMany'=>array('Phankenh')));
    	$kenhinfo = $this->Phankenh->find('all',array('recursive'=>2,'conditions'=>array('Kenh.id'=>$kenh_id),'order'=>array('Phankenh.nhanvien_id')));
    	
    	
    	$qstrcongtacdbs = "select *
    			from congtackenhs Congtackenh 
    			where Congtackenh.ngaytao >= '".$dauthang."' and Congtackenh.ngaytao <= '".$cuoithang."'   
    			AND	kenh_id in (".implode(",",$kenh_id).") and Congtackenh.ngaytao in 
    						(select max(ngaytao) 
    						from congtackenhs ctksub 
    						where ctksub.kenh_id=Congtackenh.kenh_id
    							and ctksub.ngaytao >= '".$dauthang."' 
    							and ctksub.ngaytao <= '".$cuoithang."')";
 
    	$congtackenhs = $this->Congtackenh->query($qstrcongtacdbs);
    	
    	
    	$thongtintts = $this->Thitruongkenh->find('all',array('conditions'=>array('Thitruongkenh.ngaytao >='=>$dauthang,
    																' Thitruongkenh.ngaytao <='=>$cuoithang),
    											'fields'=>array('Kenh_id',"GROUP_CONCAT(Thitruongkenh.thitruong SEPARATOR '<br> - ') as thitruong" )));
    	$chamsocs = $this->Chamsockenh->find('list',array('conditions'=>array('kenh_id'=>$kenh_id,'ngaydi >='=>$dauthang , 'ngaydi <='=>$cuoithang ),
    													'fields'=>array('kenh_id','ngaydi'),
    													'group'=>array('kenh_id','ngaydi')));
    	/*
    	$qstrthitruong ="select kenh_id, group_concat(thitruong) 
    					from thitruongkenhs as thitruongkenh
    					where kenh_id in (". implode(',', $kenh_id) .") and ngaytao >= '".$dauthang."' and ngaytao <= '".$cuoithang."'";
    	
    	$thongtintts = $this->Congtackenh->query($qstrthitruong);
    	*/
    	$this->set(compact('kenhinfo','congtackenhs','thongtintts','chamsocs','dauthang','cuoithang'));
        $this->set(compact('donvi_id','nam','thang'));
    
    	
    }
    
    
    function laykqexcel()
    {
    	
        $this->data['Congtackenh']['donvi_id']=$this->Cookie->read('cdonvi_id');
    	$this->data['Congtackenh']['nam']=$this->Cookie->read('cnam');
    	$this->data['Congtackenh']['thang']=$this->Cookie->read('cthang');
    	
    	$this->data['Congtackenh']['kenh']=$this->Cookie->read('ckenhc');
    	$this->data['kenh']=$this->Cookie->read('ckenh');
       	$this->data['Congtackenh']['nhanvien_id']=$this->Cookie->read('cnhanvien_idc');
	   	$this->data['nhanvien_id']=$this->Cookie->read('cnhanvien');
        $this->layout='';
        
        $tendv=$this->Zdv->find('first',array(
            'conditions'=>array('donvi_id'=> $this->data['Congtackenh']['donvi_id']),
            'fields'=>array('tendvcha','tendv')));
        $this->set(compact('tendv'));
        $this->laykqe();
	    		
    }
    
	function endofmonth($month=null, $year =null)
	{
	   if (!$month)
	   {
	      $month = date('m');
	   }
	   if (!$year) {
	      $year = date('Y');
	   }
	   $result = strtotime("{$year}-{$month}-01");
	   $result = strtotime('-1 second', strtotime('+1 month', $result));
	   return date('Y-m-d', $result);
	}

	function laynv()
	{
		$this->layout = 'ajax';
		
		$donvi_id = $this->data['Congtackenh']['donvi_id'];
		if($donvi_id=='')
		{
			$donvi_cha = $this->Session->read('cur_cha');
			$donvi_id = $this->Thuocdv->find('list',array('conditions'=>array('donvi_id'=>$donvi_cha),'fields'=>array('donvi','donvi')));
		}
		$nhanvien = $this->Donvi->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),
																	'fields'=>array('id','tennv')));
		$this->set('nhanvien',$nhanvien);		
	}
	
	function laykenh()
	{
		$this->layout = 'ajax';
		
		$donvi_id = $this->data['Congtackenh']['donvi_id'];
	if($donvi_id=='')
		{
			$donvi_cha = $this->Session->read('cur_cha');
			$donvi_id = $this->Thuocdv->find('list',array('conditions'=>array('donvi_id'=>$donvi_cha),'fields'=>array('donvi','donvi')));
		}
		if(isset($this->data['Congtackenh']['nhanvien_id']))
		{
			if(!empty($this->data['Congtackenh']['nhanvien_id']))
			{
				$nhanvien_id = $this->data['Congtackenh']['nhanvien_id'];
			}
			else
			{$nhanvien_id = $this->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),'fields'=>array('id')));}
		}else{
			if(!empty($this->data['nhanvien_id']))
			{
				$nhanvien_id = $this->data['nhanvien_id'];
			}
			else
			{$nhanvien_id = $this->Nhanvien->find('list',array('conditions'=>array('donvi_id'=>$donvi_id),'fields'=>array('id')));}
		}
		$kenhs = $this->Phankenh->find('list',array(
														'recursive'=>0,
														'conditions'=>array('nhanvien_id'=>$nhanvien_id,
																			'ngaykt_hd'=>null),
														'fields'=>array('Kenh.id','Kenh.tenkenh')));
		$this->set(compact('kenhs'));
	}
}
